﻿CREATE TABLE xml_data
(
  object_name character varying(128) NOT NULL PRIMARY KEY,
  object_value xml
);

CREATE OR REPLACE FUNCTION getXMLDocument(p_filename character varying)
  RETURNS xml AS
$$
SELECT CAST(pg_read_file($1 ,0, 100000000) As xml);
$$
  LANGUAGE 'sql' VOLATILE SECURITY DEFINER;

ALTER FUNCTION getxmldocument(character varying) OWNER TO geouser;
ALTER FUNCTION getxmldocument(character varying) OWNER TO postgres;

INSERT INTO xml_data(object_name, object_value)
VALUES ('AlexandriaParserReports.xml', getXMLDocument('xml/AlexandriaParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('Arlington2009ParserReports.xml', getXMLDocument('xml/Arlington2009ParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('ArlingtonParserReports.xml', getXMLDocument('xml/ArlingtonParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('Fairfax2009ParserReports.xml', getXMLDocument('xml/Fairfax2009ParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('FairfaxCityParserReports.xml', getXMLDocument('xml/FairfaxCityParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('FairfaxParserReports2008.xml', getXMLDocument('xml/FairfaxParserReports2008.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('FairfaxParserReports_2007.xml', getXMLDocument('xml/FairfaxParserReports_2007.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('FallsChurch2009ParserReports.xml', getXMLDocument('xml/FallsChurch2009ParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('FallsChurchParserReports.xml', getXMLDocument('xml/FallsChurchParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('HerndonCityParserReports.xml', getXMLDocument('xml/HerndonCityParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('Loudoun2009ParserReports.xml', getXMLDocument('xml/Loudoun2009ParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('LoudounParserReports.xml', getXMLDocument('xml/LoudounParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('ViennaTownParserReports.xml', getXMLDocument('xml/ViennaTownParserReports.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('WashingtonDCParserReports2006.xml', getXMLDocument('xml/WashingtonDCParserReports2006.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('WashingtonDCParserReports2007.xml', getXMLDocument('xml/WashingtonDCParserReports2007.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('WashingtonDCParserReports2008.xml', getXMLDocument('xml/WashingtonDCParserReports2008.xml'));

INSERT INTO xml_data(object_name, object_value)
VALUES ('WashingtonDCParserReports2009.xml', getXMLDocument('xml/WashingtonDCParserReports2009.xml'));

--XML helper function
CREATE OR REPLACE FUNCTION extract_value(
   VARCHAR,
   XML
) RETURNS TEXT AS
$$
   SELECT CASE WHEN $1 ~ '@[[:alnum:]_]+$'
   THEN (xpath($1, $2))[1]
   WHEN $1 ~* '/text()$'
   THEN (xpath($1, $2))[1]
   WHEN $1 LIKE '%/'
   THEN (xpath($1 || 'text()', $2))[1]
   ELSE (xpath($1 || '/text()', $2))[1]
   END::text;

$$ LANGUAGE 'sql' IMMUTABLE;

CREATE FUNCTION isnumeric(text) RETURNS boolean AS '
SELECT $1 ~ ''^[0-9]+$''
' LANGUAGE 'sql';

--Add crime table
CREATE TABLE crime
(
--Auto-generated key
  gid serial PRIMARY KEY, 

--General info
  crime VARCHAR(128),
  description VARCHAR(2048),
  reported DATE,
   
 --UCR information
  ucr_catagory VARCHAR(64),
  ucr_group CHAR(1),
   
 --location of crime
  street VARCHAR(256),
  city VARCHAR(64),
  state_code CHAR(2),
  zip_code INTEGER,
  county VARCHAR(64),
  location geography(POINT,4326) 
);

--Index the crime table
CREATE INDEX crime_location_idx ON crime USING GIST ( location );
  
--Test SQL
select 
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "File"]', node)) as File,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Bussiness"]', node)) as Bussiness,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,

--Uniform Crime Report (UCR)
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,

--Location
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) as Lat,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) as Lon

from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'test.xml'
) sub

--
--FallsChurch
--
insert into crime (crime, description, ucr_catagory, ucr_group, street, city, state_code, zip_code, county, location) 
select 
(substr(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node),1,128)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Location"]', node),1,256)) as Street,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="City"]', node)) as City,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="State"]', node),1,2)) as StateCode,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5))::integer as ZipCode,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,

ST_GeographyFromText('SRID=4326;POINT(' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) || ' ' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) || ')'
)
from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'FallsChurch2009ParserReports.xml'
) sub

--
--Fairfax
--
insert into crime (crime, description, ucr_catagory, ucr_group, street, city, state_code, zip_code, county, location) 
select 
(substr(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node),1,128)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Location"]', node),1,256)) as Street,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="City"]', node)) as City,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="State"]', node),1,2)) as StateCode,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5))::integer as ZipCode,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,

ST_GeographyFromText('SRID=4326;POINT(' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) || ' ' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) || ')'
)
from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'Fairfax2009ParserReports.xml'
) sub

--
-- Washington DC
--
insert into crime (crime, description, ucr_catagory, ucr_group, street, city, state_code, zip_code, county, location) 
select 
(substr(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node),1,128)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Location"]', node),1,256)) as Street,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="City"]', node)) as City,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="State"]', node),1,2)) as StateCode,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5))::integer as ZipCode,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,

ST_GeographyFromText('SRID=4326;POINT(' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) || ' ' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) || ')'
)
from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'WashingtonDCParserReports2009.xml'
) sub
WHERE 
isnumeric(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5)) is true;

--
--Loudoun
--
insert into crime (crime, description, ucr_catagory, ucr_group, street, city, state_code, zip_code, county, location) 
select 
(substr(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node),1,128)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Location"]', node),1,256)) as Street,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="City"]', node)) as City,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="State"]', node),1,2)) as StateCode,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5))::integer as ZipCode,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,

ST_GeographyFromText('SRID=4326;POINT(' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) || ' ' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) || ')'
)
from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'Loudoun2009ParserReports.xml'
) sub

--
--Arlington
--
insert into crime (crime, description, ucr_catagory, ucr_group, street, city, state_code, zip_code, county, location) 
select 
(substr(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node),1,128)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Location"]', node),1,256)) as Street,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="City"]', node)) as City,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="State"]', node),1,2)) as StateCode,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5))::integer as ZipCode,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,

ST_GeographyFromText('SRID=4326;POINT(' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) || ' ' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) || ')'
)
from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'Arlington2009ParserReports.xml'
) sub
where
extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Service"]', node) <> 'yahoo' 

--
--Alexandria
--

--
--Fairfax City
--
insert into crime (crime, description, ucr_catagory, ucr_group, street, city, state_code, zip_code, county, location) 
select 
(substr(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Catagory"]', node),1,128)) as Catagory,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "Description"]', node)) as Description,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="Catagorie"]', node)) as UCRCatagory,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.URCCatagories"]/element[@name="CrimeGroup"]', node)) as UCRGroup,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="Location"]', node),1,256)) as Street,
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="City"]', node)) as City,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="State"]', node),1,2)) as StateCode,
(substr(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/element[@name="ZipCode"]', node),1,5))::integer as ZipCode,
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "County"]', node)) as County,

ST_GeographyFromText('SRID=4326;POINT(' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LonY"]', node)) || ' ' ||
(extract_value('/class[@name="ciras.db.Crime"]/class[@name="ciras.db.Address"]/class[@name="ciras.db.GeoPoint"]/element[@name="LatX"]', node)) || ')'
)
from (
   SELECT unnest(xpath('/CrimeData/class[@name="ciras.db.Crime"]', object_value)) AS node
   FROM xml_data WHERE object_name = 'FairfaxCityParserReports.xml'
) sub
where
(extract_value('/class[@name="ciras.db.Crime"]/element[@name = "StartDate"]', node)) ~ '2009'

--
--Herndon
--

--
--Vienna 
--